package com.realtyManage.springboot.Dao;


import com.realtyManage.springboot.Model.Do.Coupon;
import com.realtyManage.springboot.Model.Do.ExchangeCoupon;
import com.realtyManage.springboot.Model.Do.User;
import io.lettuce.core.dynamic.annotation.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;

/**
 *兑换优惠劵
 *
 * @author 杭州素色网络科技有限公司
 * @since 2019-12-19
 */

@Repository
public interface ExchangeCouponRepository extends JpaRepository<ExchangeCoupon, Integer>, JpaSpecificationExecutor<ExchangeCoupon> {

    @Query(value = "SELECT * FROM exchange_coupon WHERE `code` = ?1",nativeQuery = true)
    ExchangeCoupon useCode(String code);

    @Modifying
    @Transactional
    @Query("update ExchangeCoupon a set " +
            "a.distributeType = CASE WHEN :#{#exchangeCoupon.distributeType} IS NULL THEN a.distributeType ELSE :#{#exchangeCoupon.distributeType} END, " +
            "a.userId = CASE WHEN :#{#exchangeCoupon.userId} IS NULL THEN a.userId ELSE :#{#exchangeCoupon.userId} END " +
            "where a.code = :#{#exchangeCoupon.code}")
    int update(@Param("exchangeCoupon") ExchangeCoupon exchangeCoupon);


    @Modifying
    @Transactional
    @Query("update ExchangeCoupon a set " +
            "a.scanId = CASE WHEN :#{#exchangeCoupon.scanId} IS NULL THEN a.scanId ELSE :#{#exchangeCoupon.scanId} END ," +
            "a.scanName = CASE WHEN :#{#exchangeCoupon.scanName} IS NULL THEN a.scanName ELSE :#{#exchangeCoupon.scanName} END ," +
            "a.userId = CASE WHEN :#{#exchangeCoupon.userId} IS NULL THEN a.userId ELSE :#{#exchangeCoupon.userId} END ," +
            "a.userName = CASE WHEN :#{#exchangeCoupon.userName} IS NULL THEN a.userName ELSE :#{#exchangeCoupon.userName} END ," +
            "a.propertyTye = CASE WHEN :#{#exchangeCoupon.propertyTye} IS NULL THEN a.propertyTye ELSE :#{#exchangeCoupon.propertyTye} END ," +
            "a.useDetail = CASE WHEN :#{#exchangeCoupon.useDetail} IS NULL THEN a.useDetail ELSE :#{#exchangeCoupon.useDetail} END ," +
            "a.exchangeTime = CASE WHEN :#{#exchangeCoupon.exchangeTime} IS NULL THEN a.exchangeTime ELSE :#{#exchangeCoupon.exchangeTime} END ," +
            "a.useType = CASE WHEN :#{#exchangeCoupon.useType} IS NULL THEN a.useType ELSE :#{#exchangeCoupon.useType} END " +
            "where a.id = :#{#exchangeCoupon.id}")
    int purpose(@Param("exchangeCoupon") ExchangeCoupon exchangeCoupon);

    @Query(value = "SELECT * FROM exchange_coupon WHERE user_id =?1 AND distribute_type = 0 OR phone = ?2 AND end_time >= NOW() AND use_type = 0",nativeQuery = true)
    List<ExchangeCoupon> findCouponByUserIdAndPhone(String id,String phone);

    @Query(value = "SELECT * FROM exchange_coupon WHERE user_id =?1 AND distribute_type = 1 OR phone = ?2 AND end_time >= NOW() AND use_type = 0",nativeQuery = true)
    List<ExchangeCoupon> findDCouponByUserIdAndPhone(String id,String phone);

    @Query(value = "SELECT * FROM (SELECT * FROM exchange_coupon WHERE user_id = ?1 or phone = ?2 AND end_time >= NOW() AND distribute_type = 0) a \n" +
            "WHERE a.coupon_id IS NULL",nativeQuery = true)
    List<ExchangeCoupon> findDNCouponByUserIdAndPhone(String id,String phone);

    @Query(value = "SELECT * FROM exchange_coupon WHERE user_id = ?1 AND end_time >= NOW() AND distribute_type = 1",nativeQuery = true)
    List<ExchangeCoupon> findWCouponByUserIdAndPhone(String id);

    @Modifying
    @Transactional
    @Query(value = "delete from exchange_coupon where id in (?1)",nativeQuery = true)
    int deleteExchangeCoupon(List id);

    @Query(value = "SELECT * FROM exchange_coupon WHERE scan_name = ?1 or user_name = ?1 or use_detail = ?1 ",nativeQuery = true)
    List<ExchangeCoupon> findAllByContent(String content);

    @Query(value = "SELECT COUNT(1) zs FROM `exchange_coupon`\n" +
            "UNION ALL\n" +
            "SELECT COUNT(1) sys FROM `exchange_coupon` WHERE scan_id IS NOT NULL\n" +
            "UNION ALL\n" +
            "SELECT COUNT(1) sys FROM `exchange_coupon` WHERE scan_id IS NULL\n" +
            "UNION ALL\n" +
            "SELECT COUNT(1) sys FROM `exchange_coupon` WHERE end_time <= NOW()",nativeQuery = true)
    List<String> getCouponTotal();

    @Query(value = "SELECT * FROM exchange_coupon WHERE id = ?1",nativeQuery = true)
    ExchangeCoupon findCouponByIdDetail(String id);

    @Query(value = "SELECT * FROM exchange_coupon WHERE user_id = ?1 AND distribute_type = 2 ",nativeQuery = true)
    List<ExchangeCoupon> getIntegralTicket(Integer uid);
}
